﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Data.Objects.SqlClient;


namespace DataAccess
{

    #region TeamDataAccess

    #region TeamComplexClass
    public class PlayerScore
    {
        public int? BallsFaced { get; set; }
        public int? Run1 { get; set; }
        public int? Run2 { get; set; }
        public int? Run3 { get; set; }
        public int? Run4 { get; set; }
        public int? Run6 { get; set; }
        public int? BallsBowled { get; set; }
        public int? RunsGiven { get; set; }
        public int? Wickets { get; set; }
        public int? Catches { get; set; }
        public int? Dot { get; set; }
        public int? NoBall { get; set; }
        public int? RunOutBy { get; set; }
        public string Match { get; set; }
    }
    #endregion
    public class TeamDataAccess
    {
        DataAccess.ORTIAEntitiesConn Oritia_entities;
        public IQueryable<String> GetTeams()
        {

            Oritia_entities = new ORTIAEntitiesConn();
            var team = from teams in Oritia_entities.Teams
                       select teams.TeamName;
            return team;
        }

        public List<TeamPlayerViewModel> GetTeamMembers(int seasonID, string teamName)
        {
            Oritia_entities = new ORTIAEntitiesConn();
            return Oritia_entities.GetPlayersByTeamNameAndSessionID(seasonID, teamName).ToList<TeamPlayerViewModel>();

        }

        public IQueryable<PlayerScore> GetPlayerScore(int playerID, int seasonID)
        {
            Oritia_entities = new ORTIAEntitiesConn();

            /*
             * 
             * join p in db.Player on new { ID = pts.PlayerId } equals new { ID = p.ID }
join c in db.Crew on new { ID = p.CrewId } equals new { ID = c.ID }
             * 
             */
            var playerScore = from pts in Oritia_entities.PlayerTeamSeasons
                              join p in Oritia_entities.Players on new { ID = pts.PlayerId } equals new { ID = p.ID }
                              join c in Oritia_entities.Crews on new { ID = p.CrewId } equals new { ID = c.ID }
                              join f in Oritia_entities.Fixtures on new { ID = pts.FixtureId } equals new { ID = f.Id }
                              join t1 in Oritia_entities.Teams on new { ID = f.TeamID1 } equals new { ID = t1.ID }
                              join t2 in Oritia_entities.Teams on new { ID = f.TeamID2 } equals new { ID = t2.ID }
                              where c.ID == playerID && pts.SeasonId == seasonID
                              select new PlayerScore
                              {
                                  BallsFaced = (int)pts.BallsFaced,
                                  Run1 = (int)pts.Run1,
                                  Run2 = (int)pts.Run2,
                                  Run3 = (int)pts.Run3,
                                  Run4 = (int)pts.Run4,
                                  Run6 = (int)pts.Run6,
                                  BallsBowled = (int)pts.BallsBowled,
                                  RunsGiven = (int)pts.RunsGiven,
                                  Wickets = (int)pts.Wickets,
                                  Catches = (int)pts.Catches,
                                  Dot = (int)pts.Dot,
                                  NoBall = (int)pts.NoBall,
                                  RunOutBy = (int)pts.RunOutBy,
                                  Match = (t1.TeamName + " v/s " + t2.TeamName)

                              };
            return playerScore;

        }

        public Team TeamDetails(string TeamName)
        {
            Oritia_entities = new ORTIAEntitiesConn();
            return Oritia_entities.Teams.Single<Team>(r => r.TeamName == TeamName);

        }



    }
    #endregion

    #region FixtureDataAccess
    public class FixtureModel
    {
        public string FixtureID { get; set; }
        public string Team1 { get; set; }
        public string Team2 { get; set; }
        public string Winners { get; set; }
        public string Team1Score { get; set; }
        public string Team1Wickets { get; set; }
        public string Team2Score { get; set; }
        public string Team2Wickets { get; set; }
        public string FirstBattingTeam { get; set; }
        public string SecondBattingTeam { get; set; }
    }


    public class FixtureDataAccess
    {
        DataAccess.ORTIAEntitiesConn Oritia_entities;
        public IEnumerable<FixtureModel> GetFixtures(int seasionID)
        {
            Oritia_entities = new ORTIAEntitiesConn();
            var data = from fixtures in Oritia_entities.Fixtures
                       join t1 in Oritia_entities.Teams on new { ID = fixtures.TeamID1 } equals new { ID = t1.ID }
                       join t2 in Oritia_entities.Teams on new { ID = fixtures.TeamID2 } equals new { ID = t2.ID }
                       where fixtures.SeasonId == seasionID
                       select new FixtureModel
                       {
                           Team1 = t1.TeamName,
                           Team2 = t2.TeamName,
                           Winners = SqlFunctions.StringConvert((double)(fixtures.TeamWon ?? 0)),
                           FirstBattingTeam = SqlFunctions.StringConvert((double)(fixtures.FirstBattingTeam ?? 0)),
                           SecondBattingTeam = SqlFunctions.StringConvert((double)(fixtures.SecondBattingTeam ?? 0)),
                           Team1Score = SqlFunctions.StringConvert((double)(fixtures.Team1Score ?? 0)),
                           Team1Wickets = SqlFunctions.StringConvert((double)(fixtures.Team1Wickets ?? 0)),
                           Team2Score = SqlFunctions.StringConvert((double)(fixtures.Team2Score ?? 0)),
                           Team2Wickets = SqlFunctions.StringConvert((double)(fixtures.Team2Wickets ?? 0)),
                           FixtureID = SqlFunctions.StringConvert((double)(fixtures.Id))
                       };

            return data;
        }
    }
    #endregion
}

